package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.net.ntp.TimeStamp;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInventory;
import org.compiere.model.MInventoryLine;
import org.compiere.model.MLocator;
import org.compiere.model.MProduct;
import org.compiere.model.MUOM;
import org.compiere.model.MUOMConversion;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.jboss.util.propertyeditor.BigDecimalEditor;
import org.joda.time.DateTime;

import com.lowagie.text.Font;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;
import org.utils.DataUtils;

public class MM_Inventory extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	Integer DocumentNo = null;
	String getSQlresult = null;
	
	String man1 = "";
	int man1ID = 0;
	
	String man2 = "";
	int man2ID = 0;
	
	String man3 = "";
	int man3ID = 0;
	
	String man4 = "";
	int man4ID = 0;
	
	String man5 = "";
	int man5ID = 0;
	
	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	/** */
	private String path = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		DocumentNo = getRecord_ID();
		path = Util.localFilePath;
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			// else if (name.equals("startDate"))
			// tsStartDate = (Timestamp) para[i].getParameter();
			else if (name.equals("man1")){
				man1 = (String) para[i].getInfo();
				man1ID  = Integer.parseInt((String) para[i].getParameter());
			}else if (name.equals("man2")){
				man2 = (String) para[i].getInfo();
				man2ID  = Integer.parseInt((String) para[i].getParameter());
			}else if (name.equals("man3")){
				man3 = (String) para[i].getInfo();			
				man3ID  = Integer.parseInt((String) para[i].getParameter());
			}else if (name.equals("man4")){
				man4 = (String) para[i].getInfo();
				man4ID  = Integer.parseInt((String) para[i].getParameter());
			}else if (name.equals("man5")){
				man5 = (String) para[i].getInfo();			
				man5ID  = Integer.parseInt((String) para[i].getParameter());
			}else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		
	}

	@Override
	protected String doIt() throws Exception {
		
		if(DocumentNo == null || DocumentNo <= 0)
			return Msg.translate(m_ctx, "По заданому параметру нет данных");
		
		MInventory inventory = new MInventory(Env.getCtx(), DocumentNo, get_TrxName());	

//		List<line> l = getDatas();
////		if (getSQlresult!=null)
////			return Msg.translate(m_ctx, "Ошибка запроса.");
//
//		int count = l.size(); 
////		if( count == 0)
////			return Msg.translate(m_ctx, "По заданому параметру нет данных");
		
		Label label;
		Number number;

		String linux = "";
		
		if(Env.isWindows())
			 linux = "";
		else
			linux = "/";
		
		String file = "MM_Inventory_tmpl.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));
		
		
		File templateCopy = new File(Util.localFilePath + linux + "MM_Inventory.xls");
		File temporaryFile = entry.getFile(Util.localFilePath + linux + file);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}

//		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
//		HSSFSheet sheet = wb.getSheetAt(0);
//		Row R = null;
//		Cell C = null;
		
		//
		// ExcelCell cellStart = Utils.getCellStart(tableBook,">>");
		// Style for value column
		WritableCellFormat valuestyle = new WritableCellFormat();
		valuestyle.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
		valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setAlignment(Alignment.LEFT);
		valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyle.setWrap(true);

		
		// org name ------------------------------------------------------------------
		WritableFont t10b = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
		WritableCellFormat s = new WritableCellFormat(t10b);
		s.setAlignment(Alignment.LEFT);
		s.setVerticalAlignment(VerticalAlignment.CENTRE);
		s.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);

		String v = getCtx().getProperty("#AD_Org_Name");	
		label = new Label(4, 7, v, s);
		sheet.addCell(label);

		//BIN--------------------------------------------------------------
		s = new WritableCellFormat(t10b);
		s.setAlignment(Alignment.LEFT);
		s.setVerticalAlignment(VerticalAlignment.CENTRE);
		s.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
		s.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		s.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		s.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		
		v = "";
		MBPartner bpartner = new MBPartner(Env.getCtx(), 1000084, get_TrxName());
		if(bpartner != null)
			v = bpartner.getFI_BIN_IIN();

		label = new Label(11, 7, v, s);
		sheet.addCell(label);
		
		//header-----------------------------------------------------------
		WritableFont t10 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD);
		s = new WritableCellFormat(t10);
		s.setAlignment(Alignment.CENTRE);
		s.setVerticalAlignment(VerticalAlignment.CENTRE);
		s.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
		s.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		s.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		s.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		//movementdate
		label = new Label(2, 19, inventory.getDocumentNo(), s);
		sheet.addCell(label);
		
		label = new Label(3, 19, (new java.text.SimpleDateFormat("dd.MM.yyyy").format(inventory.getMovementDate())), s);
		sheet.addCell(label);		

		s = new WritableCellFormat(t10);
		s.setAlignment(Alignment.LEFT);
		s.setVerticalAlignment(VerticalAlignment.CENTRE);
		s.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);

		sheet.mergeCells(11, 11, 15, 11);
		label = new Label(11, 11, getFIO(man1), s);
		sheet.addCell(label);
		
		String locator = "";
		// lines-------------------------------------------------------------
		List<MInventoryLine> lines = Arrays.asList(inventory.getLines(true));

		t10 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD);
		s = new WritableCellFormat(t10);
		s.setAlignment(Alignment.CENTRE);
		s.setVerticalAlignment(VerticalAlignment.CENTRE);
		s.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
		s.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		s.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		s.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);

		WritableCellFormat sl = new WritableCellFormat(t10);
		sl.setAlignment(Alignment.LEFT);
		sl.setVerticalAlignment(VerticalAlignment.CENTRE);
		sl.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
		sl.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		sl.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		sl.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);		

		WritableCellFormat sr = new WritableCellFormat(t10);
		sr.setAlignment(Alignment.RIGHT);
		sr.setVerticalAlignment(VerticalAlignment.CENTRE);
		sr.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
		sr.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		sr.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		sr.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);

		WritableCellFormat sr2 = new WritableCellFormat(t10, NumberFormats.FLOAT);
		sr2.setAlignment(Alignment.RIGHT);
		sr2.setVerticalAlignment(VerticalAlignment.CENTRE);
		sr2.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
		sr2.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		sr2.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		sr2.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);

		
		
		
		int row = 23; 
		int pos = 1;
		
		double qty, summ; qty = summ = .0;
		
		for(MInventoryLine line: lines){
			
			sheet.insertRow(row);
			
			number = new Number(0, row, pos++, sr);
			sheet.addCell(number);

			sheet.mergeCells(1, row, 2, row);
			label = new Label(1, row, line.getM_Product().getName(), sl);
			sheet.addCell(label);
			
			label = new Label(3, row, line.getM_Product().getValue(), sr);
			sheet.addCell(label);
			
			label = new Label(4, row, "", sr);
			sheet.addCell(label);
			
			label = new Label(5, row, "", sr);
			sheet.addCell(label);
			
			sheet.mergeCells(5, row, 6, row);
			label = new Label(6, row, "", sr);
			sheet.addCell(label);
			
			
			v = "";
			line.getProduct().getC_UOM_ID();
			
//			MUOMConversion m = new MUOMConversion(line.getProduct());
			MUOM mm = new MUOM(Env.getCtx(), line.getProduct().getC_UOM_ID(), get_TrxName());
//			v = mm.getName();
			
			v = mm.get_Translation(mm.COLUMNNAME_UOMSymbol);

			label = new Label(7, row, v, sr);
			sheet.addCell(label);

			
			number = new Number(8, row, line.getQtyInternalUse().doubleValue(), sr2);
			sheet.addCell(number);
			qty += line.getQtyInternalUse().doubleValue();
			
			double sum = getSum(DocumentNo, line.get_ID());
			summ += sum;
			double price = .0;
			if(line.getQtyInternalUse() != null &&  line.getQtyInternalUse().compareTo(Env.ZERO) != 0 && sum != 0)
				price = sum / line.getQtyInternalUse().doubleValue();
			
			BigDecimal bd = new BigDecimal(price).setScale(2, RoundingMode.HALF_UP);
			price = bd.doubleValue();
			
			number = new Number(9, row, price, sr2);
			sheet.addCell(number);

			bd = new BigDecimal(sum).setScale(2, RoundingMode.HALF_UP);
			sum = bd.doubleValue();
			
			sheet.mergeCells(10, row, 11, row);
			number = new Number(10, row, sum, sr2);
			sheet.addCell(number);

			sheet.mergeCells(12, row, 15, row);
			label = new Label(12, row, "", sr2);
			sheet.addCell(label);
			
			row++;
			
			MLocator mlocator =  new MLocator(Env.getCtx(), line.getM_Locator_ID(),get_TrxName());
			locator = mlocator.getValue();
			
		}
		
		
		number = new Number(8, row, qty, sr2);
		sheet.addCell(number);

		label = new Label(9, row, "", sr);
		sheet.addCell(label);
		
		sheet.mergeCells(10, row, 11, row);
		number = new Number(10, row, summ, sr2);
		sheet.addCell(number);
		
		sheet.mergeCells(12, row, 15, row);
		label = new Label(12, row, "", sr);
		sheet.addCell(label);
		
		//header------------------------
//		sheet.mergeCells(12, 19, 13, 19);
		label = new Label(11, 19, locator, s);
		sheet.addCell(label);

		
		// signers
		s = new WritableCellFormat(t10);
		s.setAlignment(Alignment.LEFT);
		s.setVerticalAlignment(VerticalAlignment.CENTRE);
		s.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		
		row += 24;
		sheet.mergeCells(11, row, 15, row);
		label = new Label(11, row, getFIO(man1), s);
		sheet.addCell(label);

		sheet.mergeCells(3, row, 6, row);
		label = new Label(3, row, getJobName(man1ID), s);
		sheet.addCell(label);
		
		
		row += 3;
		sheet.mergeCells(11, row, 15, row);
		label = new Label(11, row, getFIO(man2), s);
		sheet.addCell(label);

		sheet.mergeCells(3, row, 6, row);
		label = new Label(3, row, getJobName(man2ID), s);
		sheet.addCell(label);
		
		
		row += 2;
		sheet.mergeCells(11, row, 15, row);
		label = new Label(11, row, getFIO(man3), s);
		sheet.addCell(label);

		sheet.mergeCells(3, row, 6, row);
		label = new Label(3, row, getJobName(man3ID), s);
		sheet.addCell(label);
		
		
		row += 3;
		sheet.mergeCells(11, row, 15, row);
		label = new Label(11, row, getFIO(man4), s);
		sheet.addCell(label);

		sheet.mergeCells(3, row, 6, row);
		label = new Label(3, row, getJobName(man4ID), s);
		sheet.addCell(label);
		
		
		row += 3;
		sheet.mergeCells(11, row, 15, row);
		label = new Label(11, row, getFIO(man5), s);
		sheet.addCell(label);
		
		sheet.mergeCells(3, row, 6, row);
		label = new Label(3, row, getJobName(man5ID), s);
		sheet.addCell(label);
		

		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();

		file = Util.localFilePath + linux + "MM_Inventory.xls";
		
		if(!Env.isWindows())
			Util.launchFile(new File(file));
		else{
			try {
				String command = "excel \"" + file + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		
		return Msg.translate(m_ctx, "Success");
	}

	
	protected String getFIO(String FullName){
		String result = "";
		
		if(FullName == null || FullName.equals(""))
			return result;
		
		String[] words = FullName.split(" ");  
		
		String s = "";
		if(words.length > 0){

			if(words[0].length() >= 0)
				s += words[0];
			if(words.length >= 1 )
				if(words[1].length() >= 1)
					s += " " + words[1].substring(0, 1) + ".";
			
			if(words.length >= 2)
				if(words[2].length() >= 1)
					s += " " + words[2].substring(0, 1) + ".";			
		}
		result = s;
		return result;
	}

	protected String getJobName(int c_bpartner_id){
		String result = "";
		if(c_bpartner_id <= 0)
			return result;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = 
		"	select j.name	\n"	+ 
		"		from adempiere.hr_contract c	\n"	+
		"		left join adempiere.hr_job j on  j.hr_job_id = c.hr_job_id	\n"	+
		"		where c_bpartner_id = " + c_bpartner_id;

		try {
					pstmt = DB.prepareStatement(sql, null);

					rs = pstmt.executeQuery();
					if (rs.next()) {
						result = rs.getString(1);
					}
				} catch (SQLException e) {
					s_log.log(Level.SEVERE, "Report: FI_Form4", e);
				} finally {
					DB.close(rs, pstmt);
					rs = null;
					pstmt = null;
				}
		
		return result;
	}


	private double getSum(Integer recID, Integer lineID) {

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double result = .0;
		
		StringBuilder sql = getSQl(recID, lineID);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				 result = rs.getDouble(1);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: MM_Inventory", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
	}

	private StringBuilder getSQl(Integer rec_id, Integer line_id) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				
				"select \n" +
						" 	coalesce(sum(fa.amtacctdr),0) sum	\n" +
						"	from adempiere.fact_acct fa \n" +
						"	\n" +
						"	where  fa.ad_table_id = 321	\n" +
						"		and fa.record_id = "+	rec_id	+"	\n" +
						" 	and fa.line_id = " + line_id				
				
					);

		return sql;
	}

	
}